﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TIMKIEMNHANVIEN
{
    public class DataProvider : DataTable
    {
        //$ đối tượng quan trọng nhất
        private SqlConnection _Ketnoi;
        private SqlDataAdapter _Adapter;
        private SqlCommand _Cmd;
        private const string _Stringkn = @"Data Source=TVDKHOA-PC;Initial Catalog=QuanLyVanTai;Integrated Security=True";
        //Các properties
        public SqlConnection KetNoi
        {
            get { return _Ketnoi; }
            set { _Ketnoi = value; }
        }
        public SqlDataAdapter Adapter
        {
            get { return _Adapter; }
            set { _Adapter = value; }
        }
        public SqlCommand Cmd
        {
            get { return _Cmd; }
            set { _Cmd = value; }
        }
        //Ham Tao
        public DataProvider()
        {
            MoKetNoi();
        }
        //Các phương thức
        //Ham Load du lieu
        public void Load(SqlCommand comd)
        {
            _Cmd = comd;
            try
            {
                this.Clear();
                _Cmd.Connection = _Ketnoi;
                SqlDataAdapter ad = new SqlDataAdapter();
                ad.SelectCommand = _Cmd;
                ad.Fill(this);
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
            }
            finally
            {
                DongKetNoi();
            }
        }
        //Mo ket noi
        public bool MoKetNoi()
        {
            try
            {
                if (_Ketnoi == null)
                    _Ketnoi = new SqlConnection(_Stringkn);
                if (_Ketnoi.State == ConnectionState.Closed)
                    _Ketnoi.Open();
                return true;

            }
            catch
            {
                _Ketnoi.Close();
                return false;
            }
        }
        //Đóng kết nối
        public void DongKetNoi()
        {
            _Ketnoi.Close();
        }
        //Update du lieu
        public void CapNhat()
        {
            SqlCommandBuilder buil = new SqlCommandBuilder(_Adapter);
            _Adapter.Update(this);
        }
        //Huy vận chuyen
        public void DisposeAdapter()
        {
            _Adapter.Dispose();
        }
        //Huy ket noi
        public void DisposeKetNoi()
        {
            _Ketnoi.Dispose();
        }
        //thuc thi ko co para truyen vao
        public int ExcuteNonequery()
        {
            int kq = 0;
            SqlTransaction tr = null;
            try
            {
                tr = _Ketnoi.BeginTransaction();
                _Cmd.Connection = _Ketnoi;
                _Cmd.Transaction = tr;
                _Adapter = new SqlDataAdapter() { SelectCommand = _Cmd };
                SqlCommandBuilder buil = new SqlCommandBuilder(_Adapter);
                kq = _Adapter.Update(this);
                tr.Commit();
            }
            catch (Exception e)
            {
                if (tr != null)
                    tr.Rollback();
                XtraMessageBox.Show(e.Message);
            }
            return kq;
        }
        //thuc thi co para truyen vao
        public int ExcuteNonequery(SqlCommand cmd)
        {
            int kq = 0;
            SqlTransaction tr = null;
            try
            {
                tr = _Ketnoi.BeginTransaction();
                cmd.Connection = _Ketnoi;
                cmd.Transaction = tr;
                kq = cmd.ExecuteNonQuery();
                this.AcceptChanges();
                tr.Commit();
            }
            catch (Exception e)
            {
                if (tr != null)
                    tr.Rollback();
                throw e;
            }
            return kq;
        }
        //tra ve gia tri
        public object ExcuteScalar(SqlCommand comd)
        {
            object kq = null;
            SqlTransaction tr = null;
            try
            {
                tr = _Ketnoi.BeginTransaction();
                comd.Connection = _Ketnoi;
                comd.Transaction = tr;
                kq = comd.ExecuteScalar();
                this.AcceptChanges();
                tr.Commit();
                if (kq == DBNull.Value)
                {
                    kq = null;
                }
            }
            catch (Exception ex)
            {
                if (tr != null)
                    tr.Rollback();
                throw ex;
            }
            return kq;
        }
    }
}
